package org.lq.sm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.lq.sm.dao.SalesOrderDao;
import org.lq.sm.entity.SalesOrder;
import org.lq.sm.util.Instantiation;
import org.lq.sm.util.JDBCUtil;
/**
 * 挂单管理数据访问接口实现
 * @author 宋铀
 * @phone	1192030079
 * 
 * @package	org.lq.sm.dao.impl
 * @date 2020年8月14日下午12:20:51
 */
public class SalesOrderDaoImpl implements SalesOrderDao,Instantiation<SalesOrder>{

	/**
	 * 保存挂单
	 */
	@Override
	public int save(SalesOrder t) {
		int num = 0;//声明变量标识是否添加成功
		String sql = "INSERT INTO salesorder(id, orderid, eid, vid, gid, price, count, totalPrice, integral, status) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			//调用JDBCUtil的增删改方法
			num = JDBCUtil.executeUpdate(sql, t.getOrderid(), t.getEid(), t.getVid(), t.getGid(), t.getPrice(), t.getCount(), t.getTotalPrice(), t.getIntegral(), t.getStatus());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	/**
	 * 修改挂单
	 */
	@Override
	public int update(SalesOrder t) {
		int num = 0;//声明变量标识是否添加成功
		String sql = "update salesorder set orderid = ?, eid = ?, vid = ?, gid = ?, price = ?, count = ?, totalPrice = ?, integral = ?, status = ? where id = ?";
		//调用JDBCUtil的增删改方法
		try {
			num = JDBCUtil.executeUpdate(sql, t.getOrderid(), t.getEid(), t.getVid(), t.getGid(), t.getPrice(), t.getCount(), t.getTotalPrice(), t.getIntegral(), t.getStatus(), t.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	/**
	 * 删除挂单
	 */
	@Override
	public int delete(int id) {
		int num = 0;//声明变量标识是否添加成功
		// sql语句
		String sql = "delete from salesorder where id = ?";
		// 调用通用方法
		try {
			num = JDBCUtil.executeUpdate(sql, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;
	}
	/**
	 * 查询所有挂单
	 */
	@Override
	public List<SalesOrder> findAll() {
		// 要返回的挂单集合
		List<SalesOrder> list = null;
		// sql语句
		String sql = "select * from salesorder";
		try {
			list = JDBCUtil.executeQuery(sql, this);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 根据id查询挂单记录
	 */
	@Override
	public SalesOrder getById(int id) {
		//声明集合
		List<SalesOrder> list = null;
		//声明要返回的Sale
		SalesOrder salesOrder = null;
		// sql语句
		String sql = "select * from salesorder where id = ?";
		try {
			list = JDBCUtil.executeQuery(sql, this, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 将集合中的第一个元素赋值给salesOrder
		salesOrder = list.get(0);
		return salesOrder;
	}

	@Override
	public List<SalesOrder> getSaleOrderByStatus(int status) {

		try {
			return JDBCUtil.executeQuery("select * from salesorder where status = ?", this, status);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public SalesOrder instance(ResultSet rs) {
		SalesOrder so = new SalesOrder();
		try {
			so.setId(rs.getInt("id"));
			so.setOrderid(rs.getString("orderid"));
			so.setEid(rs.getInt("eid"));
			so.setVid(rs.getInt("vid"));
			so.setGid(rs.getInt("gid"));
			so.setPrice(rs.getDouble("price"));
			so.setCount(rs.getInt("count"));
			so.setTotalPrice(rs.getDouble("totalPrice"));
			so.setIntegral(rs.getInt("integral"));
			so.setStatus(rs.getInt("status"));
			so.setCreatedate(rs.getTimestamp("createdate"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return so;
	}


	@Override
	public Map<String, Integer> getOrderCount(int status) {
		Map<String,Integer> map = new HashMap<String, Integer>();
		String sql = "SELECT orderid,count(1) count FROM salesorder WHERE `status`= ? GROUP BY orderid";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, status);
			rs = ps.executeQuery();
			while(rs.next()) {
				map.put(rs.getString("orderid"), rs.getInt("count"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(rs!=null) {
					rs.close();
					rs = null;
				}
				if(ps!=null) {
					ps.close();
					ps = null;
				}
				if(conn!=null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}







}


















